package com.linln.modules.member.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.linln.modules.member.domain.MemberDayCount;

import java.util.Map;


/**
 * @author hct
 * @date 2019/09/12
 */
public interface MemberDayCountRepository extends JpaRepository<MemberDayCount, Long>,JpaSpecificationExecutor<MemberDayCount> {
	
	/**
	 * 定时执行数据  
	 * @return
	 */
    @Transactional
    @Modifying
	@Query(value="INSERT INTO member_daycount(area,num,create_date,update_date) SELECT area,count(area), DATE_SUB(CURDATE(), INTERVAL 1 DAY),now() FROM memberinfo where to_days(register_date) = to_days(DATE_SUB(curdate(),INTERVAL 1 DAY)) group by area ",nativeQuery = true)
	public Integer insert();
	/**
	 * 定时执行数据
	 * @return
	 */
	@Transactional
	@Query(value="SELECT count(*) FROM member_daycount where to_days(create_date) = to_days(DATE_SUB(curdate(),INTERVAL 1 DAY)) ",nativeQuery = true)
	public Integer dayCount();

	@Query(value = "SELECT DATE_FORMAT(create_date,'%Y-%m-%d') AS dayTime,SUM(num) AS daySum FROM member_daycount WHERE create_date >=  DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'), INTERVAL 30 DAY) AND create_date < DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') GROUP BY create_date",nativeQuery = true)
	public Map<String,Integer> statisticMemberDayCount();

	@Query(value = "SELECT DATE_FORMAT(create_date,'%Y-%m-%d') AS dayTime,SUM(num) AS daySum FROM store_daycount WHERE create_date >=  DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'), INTERVAL 30 DAY) AND create_date < DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') GROUP BY create_date",nativeQuery = true)
	public Map<String,Integer> statisticStoreDayCount();

	@Query(value = "SELECT DATE_FORMAT(create_date,'%Y-%m-%d') AS dayTime,SUM(num) AS daySum FROM member_pay_daycount WHERE create_date >=  DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'), INTERVAL 30 DAY) AND create_date < DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') GROUP BY create_date",nativeQuery = true)
	public Map<String,Integer> statisticMemberPayDayCount();

	@Query(value = "SELECT DATE_FORMAT(create_date,'%Y-%m-%d') AS dayTime,SUM(num) AS daySum FROM message_daycount WHERE create_date >=  DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'), INTERVAL 30 DAY) AND create_date < DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') GROUP BY create_date",nativeQuery = true)
	public Map<String,Integer> statisticMessageDayCount();

}